package com.ujn.demo.impl;

import com.alibaba.fastjson.JSON;
import com.ujn.demo.model.TbOsdModel;
import com.ujn.demo.service.OsdService;
import org.apache.http.entity.ContentType;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.text.DecimalFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Objects;

/**
 * @author sunjx
 * @program giteeCode
 * @description osd的实现类
 * @date 2020-09-01 11:55
 */
@Service
public class OsdServiceImpl implements OsdService {

    private static final Logger logger = LoggerFactory.getLogger(OsdServiceImpl.class);

    /**
     * xls格式
     */
    private static final String XLS = "xls";

    /**
     * xlsx格式
     */
    private static final String XLSX = "xlsx";

    /**
     * @description 导入excel
     * @author sunjx
     * @date 2020/9/1 14:00
     * @param file
     * @return java.util.Map<java.lang.String,com.ujn.demo.model.TbOsdModel>
     **/
    @Override
    public Map<String, TbOsdModel> importExcel(MultipartFile file) {
        Workbook workbook = null;
        try {
            // 获取Excel后缀名
            String fileName = file.getOriginalFilename();
            if (fileName == null || fileName.isEmpty() || fileName.lastIndexOf(".") < 0) {
                logger.error("解析Excel失败，因为获取到的Excel文件名非法！");
                return null;
            }
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
            // 获取Excel工作簿以及excel中的数据
            workbook = getWorkbook(file.getInputStream(), fileType);
            if (Objects.isNull(workbook)){
                logger.error("输入的不是excel格式的文件：{}", file.getOriginalFilename());
                return null;
            }
            Map<String, TbOsdModel> map = parseExcel(workbook);
            return map;
        } catch (Exception e) {
            logger.error("解析Excel失败，文件名：{},错误信息:{}", file.getOriginalFilename() , e.getMessage());
            return null;
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
            } catch (Exception e) {
                logger.error("关闭数据流出错！错误信息：{}", e.getMessage());
                return null;
            }
        }
    }

    /**
     * @description 导出excel
     * @author sunjx
     * @date 2020/9/1 15:05
     * @param
     * @return org.apache.poi.hssf.usermodel.HSSFWorkbook
     **/
    @Override
    public HSSFWorkbook exportExcel() {
        // 读取 excel 表格的路径(在resources下的excel里面)
        String realPath = "D:\\bigData\\设备列表.xlsx";
        File file = new File(realPath);
        Workbook workbook = null;
        try (InputStream inputStream = new FileInputStream(file);){
            MultipartFile multipartFile = new MockMultipartFile(file.getName(), file.getName(), ContentType.APPLICATION_OCTET_STREAM.toString(), inputStream);
            // 获取Excel后缀名
            String fileName = multipartFile.getOriginalFilename();
            if (fileName == null || fileName.isEmpty() || fileName.lastIndexOf(".") < 0) {
                logger.error("解析Excel失败，因为获取到的Excel文件名非法！");
                return null;
            }
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
            // 获取Excel工作簿以及excel中的数据
            workbook = getWorkbook(multipartFile.getInputStream(), fileType);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        Map<String, TbOsdModel> map = parseExcel(workbook);
        logger.info("导出map:{}", JSON.toJSON(map));
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("osd设备列表");
        //创建第一个单元格
        HSSFRow row = sheet.createRow(0);
        //设置行高
        row.setHeight((short) (22.50 * 20));
        //为第一个单元格设值
        row.createCell(0).setCellValue("ID");
        row.createCell(1).setCellValue("姓名");
        row.createCell(2).setCellValue("IP地址");
        row.createCell(3).setCellValue("端口");
        row.createCell(4).setCellValue("挂载路径");
        row.createCell(5).setCellValue("数据中心");
        row.createCell(6).setCellValue("设备类型");
        row.createCell(7).setCellValue("机架");
        row.createCell(8).setCellValue("容量（GB）");

        int count = 0;
        for (Map.Entry<String, TbOsdModel> entry:map.entrySet()) {
            row = sheet.createRow(count + 1);
            row.createCell(0).setCellValue(entry.getValue().getId());
            row.createCell(1).setCellValue(entry.getValue().getName());
            row.createCell(2).setCellValue(entry.getValue().getIp());
            row.createCell(3).setCellValue(entry.getValue().getPort());
            row.createCell(4).setCellValue(entry.getValue().getPath());
            row.createCell(5).setCellValue(entry.getValue().getDataCenter());
            row.createCell(6).setCellValue(entry.getValue().getType());
            row.createCell(7).setCellValue(entry.getValue().getRackNumber());
            row.createCell(8).setCellValue(entry.getValue().getCapacity());
            count++;
        }
        sheet.setDefaultRowHeight((short) (16.5 * 20));
        //列宽自适应
        for (int i = 0; i <= 9; i++) {
            sheet.autoSizeColumn(i);
        }
        // 处理中文不能自动调整列宽的问题
        this.setSizeColumn(sheet, 8);
        return wb;
    }

    /**
     * 自适应宽度(中文支持)
     * @param sheet
     * @param size
     */
    private void setSizeColumn(HSSFSheet sheet, int size) {
        for (int columnNum = 0; columnNum < size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                HSSFRow currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }

    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     * @param inputStream 读取文件的输入流
     * @param fileType 文件后缀名类型（xls或xlsx）
     * @return 包含文件数据的工作簿对象
     * @throws IOException
     */
    private Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        logger.info("OsdServiceImpl,getWorkbook，根据文件后缀名类型获取对应的工作簿对象，fileType：{}", fileType);
        Workbook workbook = null;
        if (fileType.equalsIgnoreCase(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        } else {
            return null;
        }
        return workbook;
    }

    /**
     * 解析Excel数据
     * @param workbook Excel工作簿对象
     * @return 解析结果
     */
    private Map<String, TbOsdModel> parseExcel(Workbook workbook) {
        Map<String, TbOsdModel> map = new HashMap<>();
        // 解析sheet
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }
            // 获取第一行数据
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (null == firstRow) {
                logger.error("解析Excel失败，在第一行没有读取到任何数据！");
            }
            // 解析每一行的数据，构造数据对象
            int rowStart = firstRowNum + 1;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (null == row) {
                    continue;
                }
                TbOsdModel tbOsd = convertRowToData(row);
                if (null == tbOsd) {
                    logger.error("第 " + row.getRowNum() + "行数据不合法，已忽略！");
                    continue;
                }
                //检查是否重复
                if (Objects.isNull(map.get(tbOsd.getId()))){
                    map.put(tbOsd.getId(), tbOsd);
                }
            }
        }
        logger.info("解析Excel数据,parseExcel的size:{}", map.size());
        return map;
    }

    /**
     * 提取每一行中需要的数据，构造成为一个结果数据对象
     * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
     *
     * @param row 行数据
     * @return 解析后的行数据对象，行数据错误时返回null
     */
    private TbOsdModel convertRowToData(Row row) {
        TbOsdModel tbOsd = new TbOsdModel();
        Cell cell;
        int cellNum = 0;
        // 获取ID
        cell = row.getCell(cellNum++);
        String id = convertCellValueToString(cell);
        tbOsd.setId(id);
        //获取name
        cell = row.getCell(cellNum++);
        String name = convertCellValueToString(cell);
        tbOsd.setName(name);
        //获取name
        cell = row.getCell(cellNum++);
        String ip = convertCellValueToString(cell);
        tbOsd.setIp(ip);
        //获取port
        cell = row.getCell(cellNum++);
        String port = convertCellValueToString(cell);
        tbOsd.setPort(Integer.parseInt(port));
        //获取挂载路径
        cell = row.getCell(cellNum++);
        String path = convertCellValueToString(cell);
        tbOsd.setPath(path);
        //获取数据中心
        cell = row.getCell(cellNum++);
        String dataCenter = convertCellValueToString(cell);
        tbOsd.setDataCenter(Integer.parseInt(dataCenter));
        //获取设备类型
        cell = row.getCell(cellNum++);
        String type = convertCellValueToString(cell);
        tbOsd.setType(type);
        //获取机架
        cell = row.getCell(cellNum++);
        String rack = convertCellValueToString(cell);
        tbOsd.setRackNumber(Integer.parseInt(rack));
        //获取容量
        cell = row.getCell(cellNum++);
        String capacity = convertCellValueToString(cell);
        tbOsd.setCapacity(Integer.parseInt(capacity));
        tbOsd.setCreateTime(new Date());
        tbOsd.setUpdateTime(new Date());
        logger.info("提取每一行中需要的数据，构造成为一个结果数据对象,convertRowToData,tbOsd:{JSON.toJSON}", JSON.toJSON(tbOsd));
        return tbOsd;
    }

    /**
     * 将单元格内容转换为字符串
     * @param cell
     * @return
     */
    private static String convertCellValueToString(Cell cell) {
        if(cell==null){
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            //数字
            case NUMERIC:
                Double doubleValue = cell.getNumericCellValue();
                // 格式化科学计数法，取一位整数
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);
                break;
            //字符串
            case STRING:
                returnValue = cell.getStringCellValue();
                break;
            //布尔
            case BOOLEAN:
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            // 空值
            case BLANK:
                break;
            // 公式
            case FORMULA:
                returnValue = cell.getCellFormula();
                break;
            // 故障
            case ERROR:
                break;
            default:
                break;
        }
        return returnValue;
    }
}
